﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Employees
{
    public static class EmployeeDAL
    {
        internal static EmployeeEntity GetEmployeeByReader(IDataReader dr)
        {
            EmployeeEntity employee = null;

            employee = new EmployeeEntity();
            employee.EmployeeID = dr.GetInt32(0);
            employee.Account = dr.IsDBNull(1) ? null : dr.GetString(1);
            employee.RoleID = dr.GetInt32(2);
            employee.RoleName = dr.IsDBNull(3) ? null : dr.GetString(3);
            employee.ProvinceID = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            employee.CityID = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);

            return employee;
        }

        internal static int GetEmployeeCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectEmployee = @"SELECT COUNT([EmployeeID]) FROM [Employee] INNER JOIN Role ON Employee.RoleID = Role.RoleID ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectEmployee, ref parameterlst);
            DbCommand SelectEmployeeCommand = db.GetSqlStringCommand(SelectEmployee);
            DbHelper.PackDbCommandByDbParameters(SelectEmployeeCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectEmployeeCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static IList<EmployeeEntity> GetEmployeeList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<EmployeeEntity> Employeelst = new List<EmployeeEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectEmployee = @"
                SELECT [EmployeeID],[Account],[RoleID],[Name],[ProvinceID],[CityID]
	            FROM (
                    SELECT Employee.EmployeeID, Employee.Account, Employee.RoleID, Role.Name, Role.ProvinceID, Role.CityID
                      ,ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectEmployee += " " + sortField + " " + sortMode + ",";
            SelectEmployee += @" [EmployeeID] Desc
                ) AS rowNum 
                    FROM Employee INNER JOIN Role ON Employee.RoleID = Role.RoleID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectEmployee, ref parameterlst);
            SelectEmployee += @"
                ) AS [Employee] 
	            WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectEmployeeCommand = db.GetSqlStringCommand(SelectEmployee);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectEmployeeCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectEmployeeCommand))
            {
                while (dr.Read())
                {
                    EmployeeEntity Employee = GetEmployeeByReader(dr);

                    Employeelst.Add(Employee);
                }
            }

            return Employeelst;
        }

        internal static EmployeeEntity GetEmployee(int employeeID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            EmployeeEntity employeeEntity = null;

            string SelectEmployee = @"
                SELECT Employee.EmployeeID, Employee.Account, Employee.RoleID, Role.Name, [Role].[ProvinceID],[Role].[CityID]
                FROM Employee INNER JOIN Role ON Employee.RoleID = Role.RoleID
	           ";

            DbCommand SelectEmployeeCommand = db.GetSqlStringCommand(SelectEmployee);

            SelectEmployeeCommand.CommandText += " WHERE EmployeeID=@EmployeeID ";
            db.AddInParameter(SelectEmployeeCommand, "@EmployeeID", DbType.Int32, employeeID);

            using (IDataReader dr = db.ExecuteReader(SelectEmployeeCommand))
            {
                if (dr.Read())
                {
                    employeeEntity = GetEmployeeByReader(dr);
                }
            }

            return employeeEntity;
        }

        internal static int InsertEmployee(EmployeeEntity employee)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertEmployee = @"
            INSERT INTO [Employee]
            ([Account],[Password],[RoleID])
             VALUES
            (@Account,@Password,@RoleID)";

            DbCommand InsertEmployeeCommand = db.GetSqlStringCommand(InsertEmployee);
            db.AddInParameter(InsertEmployeeCommand, "@Account", DbType.String, employee.Account);
            db.AddInParameter(InsertEmployeeCommand, "@Password", DbType.String, employee.Password);
            db.AddInParameter(InsertEmployeeCommand, "@RoleID", DbType.Int32, employee.RoleID);
            result = db.ExecuteNonQuery(InsertEmployeeCommand);

            return result;
        }

        internal static int UpdateEmployee(EmployeeEntity employee)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateEmployee = @"
            UPDATE [Employee]
            SET Account=@Account,RoleID=@RoleID
             WHERE [EmployeeID] = @EmployeeID";

            DbCommand UpdateEmployeeCommand = db.GetSqlStringCommand(UpdateEmployee);
            db.AddInParameter(UpdateEmployeeCommand, "@Account", DbType.String, employee.Account);
            db.AddInParameter(UpdateEmployeeCommand, "@RoleID", DbType.Int32, employee.RoleID);
            db.AddInParameter(UpdateEmployeeCommand, "@EmployeeID", DbType.Int32, employee.EmployeeID);
            result = db.ExecuteNonQuery(UpdateEmployeeCommand);

            return result;
        }

        internal static int DeleteEmployee(int employeeID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertEmployee = @"
            DELETE FROM [Employee] WHERE EmployeeID=@EmployeeID";

            DbCommand InsertEmployeeCommand = db.GetSqlStringCommand(InsertEmployee);
            db.AddInParameter(InsertEmployeeCommand, "@EmployeeID", DbType.Int32, employeeID);
            result = db.ExecuteNonQuery(InsertEmployeeCommand);

            return result;
        }

        internal static EmployeeEntity GetEmployee(string name, string pass)
        {
            Database db = DatabaseFactory.CreateDatabase();
            EmployeeEntity employee = null;

            string SelectUser = @"
                SELECT Employee.EmployeeID, Employee.Account, Employee.RoleID, Role.Name, [Role].[ProvinceID], [Role].[CityID]
                FROM Employee INNER JOIN Role ON Employee.RoleID = Role.RoleID
                WHERE Employee.Account=@Account AND Employee.Password=@Password
	           ";

            DbCommand SelectUserCommand = db.GetSqlStringCommand(SelectUser);
            db.AddInParameter(SelectUserCommand, "@Account", DbType.String, name);
            db.AddInParameter(SelectUserCommand, "@Password", DbType.String, pass);

            using (IDataReader dr = db.ExecuteReader(SelectUserCommand))
            {
                if (dr.Read())
                {
                    employee = GetEmployeeByReader(dr);
                }
            }

            return employee;
        }

        internal static int UpdateEmployeePass(int userID, string oldPass, string newPass)
        {
            int reslut = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SelectUser = @"
                SELECT COUNT([EmployeeID])
                FROM [Employee] WHERE Password=@Password AND EmployeeID=@EmployeeID
	           ";

            DbCommand SelectUserCommand = db.GetSqlStringCommand(SelectUser);
            db.AddInParameter(SelectUserCommand, "@Password", DbType.String, oldPass);
            db.AddInParameter(SelectUserCommand, "@EmployeeID", DbType.Int32, userID);
            object num = db.ExecuteScalar(SelectUserCommand);
            if (int.Parse(num.ToString()) > 0)
            {
                string UpdateUserPass = "update Employee set [Password]=@Password where EmployeeID=@EmployeeID";
                DbCommand UpdateUserPassCommand = db.GetSqlStringCommand(UpdateUserPass);
                db.AddInParameter(UpdateUserPassCommand, "@Password", DbType.String, newPass);
                db.AddInParameter(UpdateUserPassCommand, "@EmployeeID", DbType.Int32, userID);

                reslut = db.ExecuteNonQuery(UpdateUserPassCommand);
            }
            return reslut;
        }

        internal static int GetEmployeeRights(int userID, int rightsID, int roleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectEmployee = @"SELECT COUNT(Employee.EmployeeID)
            FROM Employee INNER JOIN
                RoleRights ON Employee.RoleID = RoleRights.RoleID
            WHERE Employee.EmployeeID=@EmployeeID AND RoleRights.RightsID=@RightsID AND RoleRights.RoleID=@RoleID";

            DbCommand SelectEmployeeCommand = db.GetSqlStringCommand(SelectEmployee);
            db.AddInParameter(SelectEmployeeCommand, "@EmployeeID", DbType.Int32, userID);
            db.AddInParameter(SelectEmployeeCommand, "@RightsID", DbType.Int32, rightsID);
            db.AddInParameter(SelectEmployeeCommand, "@RoleID", DbType.Int32, roleID);

            object obj = db.ExecuteScalar(SelectEmployeeCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static int GetEmployeeRightsGroup(int userID, int groupID, int roleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectEmployee = @"SELECT COUNT(Employee.EmployeeID)
            FROM Employee 
                INNER JOIN RoleRights ON Employee.RoleID = RoleRights.RoleID 
                INNER JOIN Rights ON RoleRights.RightsID = Rights.RightsID 
            WHERE Employee.EmployeeID=@EmployeeID AND RoleRights.RoleID=@RoleID AND [Rights].[Group]=@GroupID ";

            DbCommand SelectEmployeeCommand = db.GetSqlStringCommand(SelectEmployee);
            db.AddInParameter(SelectEmployeeCommand, "@EmployeeID", DbType.Int32, userID);
            db.AddInParameter(SelectEmployeeCommand, "@GroupID", DbType.Int32, groupID);
            db.AddInParameter(SelectEmployeeCommand, "@RoleID", DbType.Int32, roleID);

            object obj = db.ExecuteScalar(SelectEmployeeCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static int ResetPass(int adminID, string newpass)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string ResetPass = @"
            /* 1：成功 2：没有此会员 */
	        DECLARE @result AS INT
	        SET @result=1
        	
	        IF EXISTS(SELECT [EmployeeID] FROM [Employee] WHERE [EmployeeID]=@EmployeeID)
	        BEGIN
		        UPDATE [Employee] SET [Password]=@NewPass WHERE [EmployeeID]=@EmployeeID
		        SET @result=1
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
        	
	        SELECT @result";

            DbCommand ResetPassCommand = db.GetSqlStringCommand(ResetPass);
            db.AddInParameter(ResetPassCommand, "@EmployeeID", DbType.Int32, adminID);
            db.AddInParameter(ResetPassCommand, "@NewPass", DbType.String, newpass);
            object obj = db.ExecuteScalar(ResetPassCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }
    }
}
